﻿using System;
using System.Configuration;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KPIS.DBM;
using KPIS.GERP.GHRMS.MODEL;
using KPIS.GERP.GHRMS.IDAL;

namespace KPIS.GERP.GHRMS.DAL
{
    public sealed class ChildrenDAL : IChildrenDAL
    {
        IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

        public ChildrenDAL()
        {

        }
        public DataSet LoadChildrens(Nullable<int> Sequence, Nullable<int> EmployeeSequence)
        {
            string strSQL;

            DataSet ds = null;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@DeleteRecord", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@Sequence", Sequence);
                dbManager.AddParameters(2, "@empSequence", EmployeeSequence);

                strSQL = "SELECT c.CHILD_SEQ, c.CHILD_NO, c.FEE_DATE_END, c.FEE_EDU, c.CHILD_STATUS"
                        + ", c.EDU_STATUS, c.SPOUSE_SEQ, c.CHILD_TYPE, c.EMP_SEQ, c.CHILD_NAME"
                        + ", c.CHILD_LAST_NAME, c.NAME_PREFIX_SEQ, c.GENDER_SEQ, c.BIRTH_DATE"
                        + ", c.EDU_DEGREE_SEQ, c.EDU_INS_SEQ, c.RECORD_STATUS, c.CREATED_BY, c.UPDATED_BY"
                        + ", c.CITIZEN, c.DEATH_DATE, c.DEATH_REF_NO"
                        + ", CASE c.CHILD_TYPE "
                            + " WHEN '1' THEN 'บุตร'"
                            + " WHEN '2' THEN 'บุตรบุญธรรม'"
                            + " ELSE 'ไม่ระบุ'"
                        + " END AS CHILD_TYPE_NAME"
                        + ", CASE c.CHILD_STATUS "
                            + " WHEN '1' THEN 'มีชีวิตอยู่'"
                            + " WHEN '2' THEN 'เสียชีวิต'"
                            + " ELSE 'ไม่ระบุ'"
                        + " END AS CHILD_STATUS_NAME"
                        + ", CASE c.EDU_STATUS "
                            + " WHEN '1' THEN 'ศึกษาอยู่'"
                            + " WHEN '2' THEN 'ไม่ได้ศึกษา'"
                            + " ELSE 'ไม่ระบุ'"
                        + " END AS EDU_STATUS_NAME"
                        + ", CONCAT(ISNULL(np.PREFIX_NAME, ''), ISNULL(c.CHILD_NAME, ''), '  ', ISNULL(c.CHILD_LAST_NAME, '')) AS FULL_NAME"
                        + ", g.GENDER_NAME, d.EDU_DEGREE_NAME, ins.EDU_INS_NAME"
                        + ", np.PREFIX_NAME"
                    + " FROM HRM_PS_EMP_CHILDRENS c"
                    + " LEFT JOIN MAS_NAME_PREFIXES np ON c.NAME_PREFIX_SEQ = np.NAME_PREFIX_SEQ"
                    + " LEFT JOIN MAS_GENDERS g ON c.GENDER_SEQ = g.GENDER_SEQ"
                    + " LEFT JOIN MAS_EDU_DEGREES d ON c.EDU_DEGREE_SEQ = d.EDU_DEGREE_SEQ"
                    + " LEFT JOIN MAS_EDU_INSTITUTES ins ON c.EDU_INS_SEQ = ins.EDU_INS_SEQ"
                    + " WHERE c.RECORD_STATUS not like @DeleteRecord"
                    + (Sequence.HasValue ? " AND c.CHILD_SEQ = @Sequence" : "")
                    + (EmployeeSequence.HasValue ? " AND c.EMP_SEQ = @empSequence" : "");

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);                
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
            return ds;
        }
        public IList<ChildrenInfo> ListChildrens(ChildrenInfo chInfo)
        {
            string strSQL;
            int empSeq = chInfo.Employee == null ? 0 : chInfo.Employee.Sequence;

            List<ChildrenInfo> infoList = new List<ChildrenInfo>();

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@DeleteRecord", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@Sequence", chInfo.Sequence);
                dbManager.AddParameters(2, "@empSequence", empSeq);

                strSQL = "SELECT c.CHILD_SEQ, c.CHILD_NO, c.FEE_DATE_END, c.FEE_EDU, c.CHILD_STATUS"
                        + ", c.EDU_STATUS, c.SPOUSE_SEQ, c.CHILD_TYPE, c.EMP_SEQ, c.CHILD_NAME"
                        + ", c.CHILD_LAST_NAME, c.NAME_PREFIX_SEQ, c.GENDER_SEQ, c.BIRTH_DATE"
                        + ", c.EDU_DEGREE_SEQ, c.EDU_INS_SEQ, c.RECORD_STATUS, c.CREATED_BY, c.UPDATED_BY"
                        + ", c.CITIZEN"
                        + ", CASE c.CHILD_TYPE "
                            + " WHEN '1' THEN 'บุตร'"
                            + " WHEN '2' THEN 'บุตรบุญธรรม'"
                            + " ELSE 'ไม่ระบุ'"
                        + " END AS CHILD_TYPE_NAME"
                        + ", CASE c.CHILD_STATUS "
                            + " WHEN '1' THEN 'มีชีวิตอยู่'"
                            + " WHEN '2' THEN 'เสียชีวิต'"
                            + " ELSE 'ไม่ระบุ'"
                        + " END AS CHILD_STATUS_NAME"
                        + ", CASE c.EDU_STATUS "
                            + " WHEN '1' THEN 'ศึกษาอยู่'"
                            + " WHEN '2' THEN 'ไม่ได้ศึกษา'"
                            + " ELSE 'ไม่ระบุ'"
                        + " END AS EDU_STATUS_NAME"
                        + ", c.DEATH_DATE, c.DEATH_REF_NO"
                    + " FROM HRM_PS_EMP_CHILDRENS c"
                    + " WHERE c.RECORD_STATUS not like @DeleteRecord"
                    + (chInfo.Sequence > 0 ? " AND c.CHILD_SEQ = @Sequence" : "")
                    + (empSeq > 0 ? " AND c.EMP_SEQ = @empSequence" : "");

                IDataReader rdr = dbManager.ExecuteReader(CommandType.Text, strSQL);

                while (rdr.Read())
                {
                    HisSpouseDAL spouDAL = new HisSpouseDAL();
                    HisSpouseInfo spouInfo = new HisSpouseInfo();
                    if (!rdr.IsDBNull(6))
                    {
                        IList<HisSpouseInfo> spouList = spouDAL.ListHisSpouses(new HisSpouseInfo(rdr.GetInt32(6)));
                        spouInfo = spouList[0];
                    }

                    EmployeeDAL empDAL = new EmployeeDAL();
                    EmployeeInfo empInfo = new EmployeeInfo();
                    if (!rdr.IsDBNull(8))
                    {
                        IList<EmployeeInfo> empList = empDAL.ListEmployees(new EmployeeInfo(rdr.GetInt32(8)));
                        empInfo = empList[0];
                    }

                    MasNamePrefixDAL pfDAL = new MasNamePrefixDAL();
                    MasNamePrefixInfo pfInfo = new MasNamePrefixInfo();
                    if (!rdr.IsDBNull(11))
                    {
                        IList<MasNamePrefixInfo> pfList = pfDAL.ListNamePrefixes(new MasNamePrefixInfo(rdr.GetInt32(11)));
                        pfInfo = pfList[0];
                    }

                    MasGenderDAL genderDAL = new MasGenderDAL();
                    MasGenderInfo genderInfo = new MasGenderInfo();
                    if (!rdr.IsDBNull(12))
                    {
                        IList<MasGenderInfo> genderList = genderDAL.ListMasGenders(new MasGenderInfo(rdr.GetInt32(12)));
                        genderInfo = genderList[0];
                    }

                    MasEducationDegreeDAL degreeDAL = new MasEducationDegreeDAL();
                    MasEducationDegreeInfo degreeInfo = new MasEducationDegreeInfo();
                    if (!rdr.IsDBNull(14))
                    {
                        IList<MasEducationDegreeInfo> degreeList = degreeDAL.ListEducationDegree(new MasEducationDegreeInfo(rdr.GetInt32(14)));
                        degreeInfo = degreeList[0];
                    }

                    MasEducationInstituteDAL insDAL = new MasEducationInstituteDAL();
                    MasEducationInstituteInfo insInfo = new MasEducationInstituteInfo();
                    if (!rdr.IsDBNull(15) && rdr.GetInt32(15) > 0)
                    {
                        IList<MasEducationInstituteInfo> insList = insDAL.ListEducationInstitutes(new MasEducationInstituteInfo(rdr.GetInt32(15)));
                        insInfo = insList[0];
                    }

                    PersonInfo psInfo = new PersonInfo(rdr.IsDBNull(9) ? "" : rdr.GetString(9)
                                , rdr.IsDBNull(10) ? "" : rdr.GetString(10)
                                , "", "", ""
                                , rdr.IsDBNull(20) ? "" : rdr.GetString(20)
                                , "", "", ""
                                , pfInfo, genderInfo
                                , null, null, null
                                , rdr.IsDBNull(13) ? (DateTime?)null : rdr.GetDateTime(13)
                                , null
                                , rdr.IsDBNull(23) ? (DateTime?)null : rdr.GetDateTime(23));
                    psInfo.DeathRefNo = rdr.IsDBNull(24) ? "" : rdr.GetString(24);

                    ChildrenInfo Info = new ChildrenInfo(rdr.GetInt32(0)
                            , rdr.IsDBNull(1) ? 0 : rdr.GetInt32(1)
                            , rdr.IsDBNull(2) ? (DateTime?)null : rdr.GetDateTime(2)
                            , rdr.GetString(3) == "1"
                            , rdr.IsDBNull(4) ? "" : rdr.GetString(4)
                            , rdr.IsDBNull(5) ? "" : rdr.GetString(5)
                            , spouInfo
                            , rdr.IsDBNull(7) ? "" : rdr.GetString(7)
                            , empInfo
                            , psInfo
                            , degreeInfo, insInfo
                            , rdr.IsDBNull(16) ? "" : rdr.GetString(16)
                            , rdr.IsDBNull(17) ? (int?)null : rdr.GetInt32(17)
                            , rdr.IsDBNull(18) ? (int?)null : rdr.GetInt32(18));

                    Info.StatusName = rdr.IsDBNull(21) ? "" : rdr.GetString(21);
                    infoList.Add(Info);
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
            return infoList;
        }

        public void Insert(ChildrenInfo Info)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(21);

                dbManager.AddParameters(0, "@EmpSeq", Info.Employee == null ? (int?)null : Info.Employee.Sequence);
                dbManager.AddParameters(1, "@ChildNo", Info.No);
                dbManager.AddParameters(2, "@GenderSeq", Info.Person.Gender == null ? (int?)null : Info.Person.Gender.Sequence);
                dbManager.AddParameters(3, "@PrefixSeq", Info.Person.NamePrefix == null ? (int?)null : Info.Person.NamePrefix.Sequence);
                dbManager.AddParameters(4, "@ChildName", Info.Person.FirstName);
                dbManager.AddParameters(5, "@ChildLastName", Info.Person.LastName);
                dbManager.AddParameters(6, "@BirthDate", Info.Person.BirthDate);
                dbManager.AddParameters(7, "@ChildStatus", Info.Status);
                dbManager.AddParameters(8, "@EduStatus", Info.EducationStatus);
                dbManager.AddParameters(9, "@FeeDateEnd", Info.FeeDateEnd);
                dbManager.AddParameters(10, "@FeeEdu", Info.EducationFee ? "1" : "0");
                dbManager.AddParameters(11, "@SpouseSeq", Info.Spouse == null ? (int?)null : Info.Spouse.Sequence);
                dbManager.AddParameters(12, "@EduDegreeSeq", Info.EducationDegree == null ? (int?)null : Info.EducationDegree.Sequence);
                dbManager.AddParameters(13, "@EduInsSeq", Info.EducationInstitute == null ? (int?)null : Info.EducationInstitute.Sequence);
                dbManager.AddParameters(14, "@ChildTypeSeq", Info.Type);
                dbManager.AddParameters(15, "@Citizen", Info.Person.Citizen);

                dbManager.AddParameters(16, "@DeathDate", Info.Person.DeathDate);
                dbManager.AddParameters(17, "@DeathRefNo", Info.Person.DeathRefNo);

                dbManager.AddParameters(18, "@RecordStatus", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(19, "@CreatedBy", Info.CreatedBy);
                dbManager.AddParameters(20, "@CreatedWhen", DateTime.Today);

                strSQL = "INSERT INTO HRM_PS_EMP_CHILDRENS(EMP_SEQ, CHILD_NO, GENDER_SEQ, NAME_PREFIX_SEQ"
                        + ", CHILD_NAME, CHILD_LAST_NAME, BIRTH_DATE, CHILD_STATUS, EDU_STATUS, CITIZEN"
                        + ", FEE_DATE_END, FEE_EDU, SPOUSE_SEQ, EDU_DEGREE_SEQ, EDU_INS_SEQ, CHILD_TYPE"
                        + ", DEATH_DATE, DEATH_REF_NO"
                        + ", RECORD_STATUS, CREATED_BY, CREATED_WHEN)"
                    + " VALUES(@EmpSeq, @ChildNo, @GenderSeq, @PrefixSeq, @ChildName"
                        + ", @ChildLastName, @BirthDate, @ChildStatus, @EduStatus, @Citizen"
                        + ", @FeeDateEnd, @FeeEdu, @SpouseSeq, @EduDegreeSeq, @EduInsSeq, @ChildTypeSeq"
                        + ", @DeathDate, @DeathRefNo, @RecordStatus, @CreatedBy, @CreatedWhen)";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
        }

        public void Update(ChildrenInfo Info)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(21);

                dbManager.AddParameters(0, "@EmpSeq", Info.Employee == null ? (int?)null : Info.Employee.Sequence);
                dbManager.AddParameters(1, "@ChildNo", Info.No);
                dbManager.AddParameters(2, "@GenderSeq", Info.Person.Gender == null ? (int?)null : Info.Person.Gender.Sequence);
                dbManager.AddParameters(3, "@PrefixSeq", Info.Person.NamePrefix == null ? (int?)null : Info.Person.NamePrefix.Sequence);
                dbManager.AddParameters(4, "@ChildName", Info.Person.FirstName);
                dbManager.AddParameters(5, "@ChildLastName", Info.Person.LastName);
                dbManager.AddParameters(6, "@BirthDate", Info.Person.BirthDate);
                dbManager.AddParameters(7, "@ChildStatus", Info.Status);
                dbManager.AddParameters(8, "@EduStatus", Info.EducationStatus);
                dbManager.AddParameters(9, "@FeeDateEnd", Info.FeeDateEnd);
                dbManager.AddParameters(10, "@FeeEdu", Info.EducationFee ? "1" : "0");
                dbManager.AddParameters(11, "@SpouseSeq", Info.Spouse == null ? (int?)null : Info.Spouse.Sequence);
                dbManager.AddParameters(12, "@EduDegreeSeq", Info.EducationDegree == null ? (int?)null : Info.EducationDegree.Sequence);
                dbManager.AddParameters(13, "@EduInsSeq", Info.EducationInstitute == null ? (int?)null : Info.EducationInstitute.Sequence);
                dbManager.AddParameters(14, "@ChildTypeSeq", Info.Type);
                dbManager.AddParameters(15, "@ChildSeq", Info.Sequence);
                dbManager.AddParameters(16, "@Citizen", Info.Person.Citizen);
                dbManager.AddParameters(17, "@DeathDate", Info.Person.DeathDate);
                dbManager.AddParameters(18, "@DeathRefNo", Info.Person.DeathRefNo);
                dbManager.AddParameters(19, "@UpdatedBy", Info.UpdatedBy);
                dbManager.AddParameters(20, "@UpdatedWhen", DateTime.Today);

                strSQL = "UPDATE HRM_PS_EMP_CHILDRENS"
                           + " SET EMP_SEQ = @EmpSeq"
                                + ", CHILD_NO = @ChildNo"
                                + ", GENDER_SEQ = @GenderSeq"
                                + ", NAME_PREFIX_SEQ = @PrefixSeq"
                                + ", CHILD_NAME = @ChildName"
                                + ", CHILD_LAST_NAME = @ChildLastName"
                                + ", BIRTH_DATE = @BirthDate"
                                + ", CHILD_STATUS = @ChildStatus"
                                + ", EDU_STATUS = @EduStatus"
                                + ", FEE_DATE_END = @FeeDateEnd"
                                + ", FEE_EDU = @FeeEdu"
                                + ", SPOUSE_SEQ = @SpouseSeq"
                                + ", EDU_DEGREE_SEQ = @EduDegreeSeq"
                                + ", EDU_INS_SEQ = @EduInsSeq"
                                + ", CHILD_TYPE = @ChildTypeSeq"
                                + ", CITIZEN = @Citizen"
                                + ", DEATH_DATE = @DeathDate"
                                + ", DEATH_REF_NO = @DeathRefNo"
                                + ", UPDATED_BY = @UpdatedBy"
                                + ", UPDATED_WHEN = @UpdatedWhen"
                           + " WHERE CHILD_SEQ = @ChildSeq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
        }

        public void Delete(int Sequence, int UpdatedBy)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@ChildSeq", Sequence);
                dbManager.AddParameters(1, "@RecordStatus", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(2, "@UpdatedBy", UpdatedBy);
                dbManager.AddParameters(3, "@UpdatedWhen", DateTime.Today);

                strSQL = "UPDATE HRM_PS_EMP_CHILDRENS"
                    + " SET RECORD_STATUS = @RecordStatus"
                        + ", UPDATED_BY = @UpdatedBy"
                        + ", UPDATED_WHEN = @UpdatedWhen"
                    + " WHERE CHILD_SEQ = @ChildSeq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
        }
    }
}
